import mysql.connector
from mysql.connector import Error


def get_random_dishes_from_same_canteen():
    """从同一食堂随机获取3个菜品"""
    try:
        # 数据库连接配置
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="123456",
            database="esl"
        )

        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)

            # Step 1: 随机选取一个拥有至少3个菜品的食堂
            canteen_query = """
            SELECT canteen_id 
            FROM dish 
            GROUP BY canteen_id 
            HAVING COUNT(*) >= 3 
            ORDER BY RAND() 
            LIMIT 1
            """
            cursor.execute(canteen_query)
            canteen = cursor.fetchone()

            if not canteen:
                print("没有符合条件的食堂（所有食堂菜品不足3个）")
                return []

            # Step 2: 从选中的食堂中随机取3个菜品
            dish_query = """
            SELECT dish_id, name, price, canteen_id 
            FROM dish 
            WHERE canteen_id = %s 
            ORDER BY RAND() 
            LIMIT 3
            """
            cursor.execute(dish_query, (canteen['canteen_id'],))
            dishes = cursor.fetchall()

            return dishes

    except Error as e:
        print("数据库错误:", e)
        return []
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()


# 使用示例
if __name__ == "__main__":
    random_dishes = get_random_dishes_from_same_canteen()

    if random_dishes:
        print("同一食堂随机菜品：")
        for dish in random_dishes:
            print(f"食堂ID: {dish['canteen_id']}")
            print(f"菜品ID: {dish['dish_id']} | 名称: {dish['name']} | 价格: ¥{dish['price']}")
            print("-------------------")
    else:
        print("未获取到符合条件的菜品")